﻿using System;
using System.Collections.Generic;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.IO;
using System.Data;

public class MSExcelUtil
{
    string _FileName = "";
    HSSFWorkbook workbook;
    string _template = "";

    public MSExcelUtil(string path)
    {
        using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
        {
            workbook = new HSSFWorkbook(file);
        }
    }

    public MSExcelUtil(string FileName, string TemplateName, Stream Template, string Subject)
    {
        //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock.
        //book1.xls is an Excel-2007-generated file, so some new unknown BIFF records are added. 
        //FileStream file = new FileStream(Template, FileMode.Open, FileAccess.Read);
        _template = TemplateName;
        workbook = new HSSFWorkbook(Template);
        _FileName = FileName;

        //create a entry of DocumentSummaryInformation
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "ISE";
        workbook.DocumentSummaryInformation = dsi;

        //create a entry of SummaryInformation
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = Subject;
        workbook.SummaryInformation = si;
    }

    public MSExcelUtil(string FileName, string Subject)
    {
        workbook = new HSSFWorkbook();
        _FileName = FileName;

        //create a entry of DocumentSummaryInformation
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "ISE";
        workbook.DocumentSummaryInformation = dsi;

        //create a entry of SummaryInformation
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Subject = Subject;
        workbook.SummaryInformation = si;
    }

    float MeasureTextWidth(string sText, System.Drawing.Font f)
    {
        System.Drawing.Bitmap i = new System.Drawing.Bitmap(1, 1);
        System.Drawing.Graphics g = System.Drawing.Graphics.FromImage(i);
        System.Drawing.SizeF fSize = g.MeasureString(sText, f);
        return fSize.Width;
    }
    
    void SetColumnWidth(ISheet worksheet, List<String> colText)
    {
        for (Int32 i = 0; i < colText.Count; i++)
        {
            //worksheet.AutoSizeColumn(i);
            float w = MeasureTextWidth(colText[i], new System.Drawing.Font("Arial", 10f));
            w = MeasureTextWidth(colText[i], new System.Drawing.Font("Arial", 10f));
            if (w > 400)
                w = 400;
            worksheet.SetColumnWidth(i, Convert.ToInt32(w * 35.5));
        }
    }
    
    string WriteFile()
    {
        try
        {
            //Write the stream data of workbook to the root directory
            _FileName = Path.GetTempFileName() + _FileName + ".xls";
            FileStream file = new FileStream(_FileName, FileMode.Create);

            workbook.Write(file);
            file.Close();

        }
        catch (Exception ex)
        {
            _FileName = "";
            throw new Exception(ex.Message);
        }
        return _FileName;
    }

    void DatatableToCell(out List<String> colContent, ISheet worksheet, DataTable dt)
    {
        List<String> colText = new List<string>();
        IRow rowE = worksheet.CreateRow(0);
        ICell cellE;
        IFont font1 = workbook.CreateFont();
        font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
        ICellStyle cellHeaderStyle = workbook.CreateCellStyle();
        cellHeaderStyle.SetFont(font1);

        //Create header
        foreach (DataColumn clm in dt.Columns)
        {
            cellE = rowE.CreateCell(clm.Ordinal);
            cellE.CellStyle = cellHeaderStyle;
            cellE.SetCellValue(clm.Caption);
            colText.Add(clm.Caption);
        }

        int iIndex = 0;
        ICellStyle cellDateStyle = workbook.CreateCellStyle();
        cellDateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd/MM/yyyy HH:mm");

        ICellStyle cellIntStyle = workbook.CreateCellStyle();
        cellIntStyle.DataFormat = workbook.CreateDataFormat().GetFormat("###0");

        ICellStyle cellNumStyle = workbook.CreateCellStyle();
        cellNumStyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.0#");

        foreach (DataRow row in dt.Rows)
        {
            rowE = worksheet.CreateRow(iIndex + 1);
            for (int i = 0; i < dt.Columns.Count; i++)
            {

                String displayValue = "";
                switch (dt.Columns[i].DataType.Name)
                {
                    case "DateTime":
                        cellE = rowE.CreateCell(dt.Columns[i].Ordinal);
                        if (row[i] != DBNull.Value)
                            cellE.SetCellValue((DateTime)row[i]);

                        cellE.CellStyle = cellDateStyle;
                        if (row[i] != DBNull.Value)
                            displayValue = ((DateTime)row[i]).ToString("dd/MM/yyyy HH:mm");
                        break;
                    case "Boolean":
                    case "Int32":
                        cellE = rowE.CreateCell(dt.Columns[i].Ordinal, CellType.NUMERIC);
                        if (row[i] != DBNull.Value)
                        {
                            cellE.SetCellValue(Convert.ToInt32(row[i]));
                            displayValue = row[i].ToString();
                        }
                        cellE.CellStyle = cellIntStyle;
                        break;
                    default:
                        if (dt.Columns[i].DataType.IsValueType)
                        {
                            cellE = rowE.CreateCell(dt.Columns[i].Ordinal, CellType.NUMERIC);
                            if (row[i] != DBNull.Value)
                                cellE.SetCellValue(Convert.ToDouble(row[i]));

                            cellE.CellStyle = cellNumStyle;
                            if (row[i] != DBNull.Value)
                            {
                                displayValue = row[i].ToString();
                                if (displayValue.EndsWith(".00"))
                                    displayValue = displayValue.Substring(0, displayValue.Length - 3);
                            }
                        }
                        else
                        {
                            if (row[i] == DBNull.Value)
                                cellE = rowE.CreateCell(dt.Columns[i].Ordinal);
                            else
                            {
                                rowE.CreateCell(dt.Columns[i].Ordinal).SetCellValue(row[i].ToString());
                                displayValue = row[i].ToString();
                            }
                        }
                        break;
                }
                if (colText[dt.Columns[i].Ordinal].Length < displayValue.Length)
                    colText[dt.Columns[i].Ordinal] = displayValue;
            }
            iIndex++;
        }
        colContent = colText;

        //Add DateExport
        string DateExport = "Xuất ngày: " + DateTime.Now.ToString("dd/MM/yyyy hh:mm tt");
        InsertRows(ref worksheet, 0, 1);
        rowE = worksheet.CreateRow(0);
        cellE = rowE.CreateCell(0);
        cellE.SetCellValue(DateExport);
        cellE.CellStyle = cellHeaderStyle;
        //Merge cells
        CellRangeAddress range = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
        worksheet.AddMergedRegion(range);

        //Add TotalRows
        string TotalRows = "Tổng số dòng: " + dt.Rows.Count.ToString();
        InsertRows(ref worksheet, 1, 1);
        rowE = worksheet.CreateRow(1);
        cellE = rowE.CreateCell(0);
        cellE.SetCellValue(TotalRows);
        cellE.CellStyle = cellHeaderStyle;
        //Merge cells
        range = new CellRangeAddress(1, 1, 0, dt.Columns.Count - 1);
        worksheet.AddMergedRegion(range);
    }

    public DataTable GetSheet()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        for (int sheetNo = 0; sheetNo < workbook.NumberOfSheets; sheetNo++)
        {
            DataRow dr = dt.NewRow();
            dr["ID"] = sheetNo;
            dr["Name"] = workbook.GetSheetName(sheetNo);
            dt.Rows.Add(dr);
        }

        return dt;
    }

    public DataTable ReadData(int SheetIdx)
    {
        ISheet sheet = workbook.GetSheetAt(SheetIdx);
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

        DataTable dt = new DataTable();

        int isFirst = 0;
        try
        {


            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                if (isFirst == 0)
                {
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            dt.Columns.Add(cell.ToString());

                        }
                        else
                            dt.Columns.Add("col" + j.ToString());
                    }
                }
                else
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {

                            if (cell.CellType == CellType.NUMERIC)
                            {
                                if (DateUtil.IsCellDateFormatted(cell))
                                    dr[i] = cell.DateCellValue.ToString("dd/MM/yyyy HH:mm:ss");
                                else
                                    dr[i] = cell.NumericCellValue.ToString();
                            }
                            else if (cell.CellType == CellType.FORMULA)
                                dr[i] = cell.NumericCellValue.ToString();
                            else
                                dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                isFirst++;
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return dt;
    }

    public DataTable ReadData()
    {
        ISheet sheet = workbook.GetSheetAt(0);
        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

        DataTable dt = new DataTable();

        int isFirst = 0;
        try
        {


            while (rows.MoveNext())
            {
                IRow row = (IRow)rows.Current;
                if (isFirst == 0)
                {
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell != null)
                        {
                            dt.Columns.Add(cell.ToString());

                        }
                        else
                            dt.Columns.Add("col" + j.ToString());
                    }
                }
                else
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {

                            if (cell.CellType == CellType.NUMERIC)
                            {
                                if (DateUtil.IsCellDateFormatted(cell))
                                    dr[i] = cell.DateCellValue.ToString("dd/MM/yyyy HH:mm:ss");
                                else
                                    dr[i] = cell.NumericCellValue.ToString();
                            }
                            else if (cell.CellType == CellType.FORMULA)
                                dr[i] = cell.NumericCellValue.ToString();
                            else
                                dr[i] = cell.ToString();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                isFirst++;
            }
        }
        catch (Exception ex)
        {

            throw new Exception(ex.Message);
        }
        return dt;
    }

    void InsertRows(ref ISheet sheet1, int fromRowIndex, int rowCount)
    {
        //sheet1.CreateRow(fromRowIndex);

        sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false);

        for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
        {
            IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
            IRow rowInsert = sheet1.CreateRow(rowIndex);
            int firstColMerge = -1;
            int endColMerge = 0;
            rowInsert.Height = rowSource.Height;
            for (int colIndex = 0; colIndex <= rowSource.LastCellNum; colIndex++)
            {
                ICell cellSource = rowSource.GetCell(colIndex);
                ICell cellInsert = rowInsert.CreateCell(colIndex);
                if (cellSource != null)
                {
                    if (cellSource.CellType == CellType.STRING)
                    {
                        endColMerge = colIndex - 1;
                        cellInsert.SetCellValue(cellSource.StringCellValue);
                        if (firstColMerge >= 0 && endColMerge > firstColMerge)
                            sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, firstColMerge, endColMerge));

                        firstColMerge = colIndex;
                    }
                    cellInsert.CellStyle = cellSource.CellStyle;
                }
                else if (cellSource == null && colIndex == rowSource.LastCellNum - 1)
                {
                    endColMerge = colIndex;
                    if (firstColMerge >= 0 && endColMerge > firstColMerge)
                        sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, firstColMerge, endColMerge - 1));

                }
            }
        }
    }

    public string Export(DataTable dt, string SheetName)
    {
        ISheet worksheet = workbook.CreateSheet(SheetName);

        List<String> colText = new List<string>();

        DatatableToCell(out colText, worksheet, dt);

        //
        SetColumnWidth(worksheet, colText);
        //
        return WriteFile();
    }

    public string Export(DataTable dt, string SheetName, params string[] parameterHeaders)
    {
        ISheet worksheet = workbook.CreateSheet(SheetName);

        List<String> colText = new List<string>();

        DatatableToCell(out colText, worksheet, dt);

        //Set CellStyle
        IFont font1 = workbook.CreateFont();
        font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
        ICellStyle cellHeaderStyle = workbook.CreateCellStyle();
        cellHeaderStyle.SetFont(font1);

        IRow rowE;
        ICell cellE;

        if (parameterHeaders != null)
        {
            int i = 0;
            foreach (string obj in parameterHeaders)
            {
                InsertRows(ref worksheet, i, 1);
                rowE = worksheet.CreateRow(i);
                cellE = rowE.CreateCell(0);
                cellE.SetCellValue(obj);
                cellE.CellStyle = cellHeaderStyle;

                //Merge cells
                CellRangeAddress range = new CellRangeAddress(i, i, 0, dt.Columns.Count-1);
                worksheet.AddMergedRegion(range);
                i++;
            }
        }

        //
        SetColumnWidth(worksheet, colText);

        //
        return WriteFile();
    }

    public string Export(DataSet ds, params string[] parameterHeaders)
    {

        for (int j = 0; j < ds.Tables.Count; j++)
        {
            DataTable dt = ds.Tables[j];
            string SheetName = "Sheet" + (j + 1).ToString();

            ISheet worksheet = workbook.CreateSheet(SheetName);

            List<String> colText = new List<string>();

            DatatableToCell(out colText, worksheet, dt);

            //Set CellStyle
            IFont font1 = workbook.CreateFont();
            font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
            ICellStyle cellHeaderStyle = workbook.CreateCellStyle();
            cellHeaderStyle.SetFont(font1);

            IRow rowE;
            ICell cellE;

            if (parameterHeaders != null)
            {
                int i = 0;
                foreach (string obj in parameterHeaders)
                {
                    InsertRows(ref worksheet, i, 1);
                    rowE = worksheet.CreateRow(0);
                    cellE = rowE.CreateCell(0);
                    cellE.SetCellValue(obj);
                    cellE.CellStyle = cellHeaderStyle;

                    //Merge cells
                    CellRangeAddress range = new CellRangeAddress(i, i, 0, dt.Columns.Count - 1);
                    worksheet.AddMergedRegion(range);
                    i++;
                }
            }

            //
            SetColumnWidth(worksheet, colText);
        }

        return WriteFile();
    }

    


}
